"""
这里对数据源文件进行处理
进行入库操作
"""
import base64
import pymysql
import os
import pandas as pd


file_names = {"food": "", "org": "organizationInfo", "window": ""}
headers = {
    "food": ["id", "window_id", "type", "name", "price", "status", "diningType", "unit", "image_id", "description"],
    "org": ["id", "name", "address", "creditCard"],
    "window": ["id", "name", "address", "organization_id", "machine_id", "status"]
}

def insert_image(path: str = "./data"):
    # 连接数据库
    _db = pymysql.connect(host='120.24.60.17', port=3306, user="root", passwd="1234", db="res")
    # 使用cursor()方法获取操作游标
    _cursor = _db.cursor()

    for img in os.listdir(path):
        if img.title()[-3:] in ["Png", "jpg"]:
            with open(path + img.title(), "rb") as _f:
                # 转为二进制格式，并且使用base64进行加密
                base64_data = base64.b64encode(_f.read())

            # print(img.title()[:-4])
            # print(img.title(), type(img.title()))
            sql = "INSERT INTO picture(id,size,content) VALUE (%s,%s,%s)"
            try:
                # 执行SQL语句
                # cursor.execute(sql)
                _cursor.execute(sql, [img.title()[:-4], len(base64_data), base64_data])
                # 提交事务
                _db.commit()
                print("成功插入图片：" + img.title() + "  长度为" + str(len(base64_data)))
            except Exception as e:
                print("异常！", str(e))
                # 有异常，回滚事务
                _db.rollback()

    _cursor.close()
    _db.close()


def insert_window():
    # 连接数据库
    _db = pymysql.connect(host='120.24.60.17', port=3306, user="root", passwd="1234", db="res")
    # 使用cursor()方法获取操作游标
    _cursor = _db.cursor()
    global headers
    df0 = pd.read_csv("./data/window.csv", sep=',', encoding='utf-8', names=headers["window"])

    # sql = "INSERT INTO WindowInfo(id, name, address, organization_id, status) VALUES (%s,%s,%s,%s,%s)"
    for index, row in df0.iterrows():
        # print("成功插入窗口：" + sql)
        sql = "INSERT INTO WindowInfo(id, name, address, organization_id, status) VALUE "
        sql += '(\'{}\',\'{}\',\'{}\',\'{}\',\'{}\');'.format(int(row["id"]), row["name"], row["address"],
                                                              int(row["organization_id"]), str(row["status"]))
        try:
            # 执行SQL语句
            # "id", "name", "address", "organization_id", "machine_id", "status"
            # print(int(row["status"]))
            # cursor.execute(sql, [row["id"], row["name"], row["address"], row["organization_id"], str(row["status"])])
            _cursor.execute(sql)
            # 提交事务
            _db.commit()
            print("成功插入窗口：" + sql)
        except Exception as e:
            print("异常！", str(e), "sql", sql)
            # 有异常，回滚事务
            _db.rollback()

    _cursor.close()
    _db.close()


def insert_saleUnit():
    # 连接数据库
    _db = pymysql.connect(host='120.24.60.17', port=3306, user="root", passwd="1234", db="res")
    # 使用cursor()方法获取操作游标
    _cursor = _db.cursor()
    global headers
    df0 = pd.read_csv("./data/food.csv", sep=',', encoding='utf-8', names=headers["food"])

    for index, row in df0.iterrows():

        # print('>>>',str(row["status"]),type(str(row["status"])))
        sql = "INSERT INTO saleUnit(id,window_id,type,name,price,status,diningType,unit,image_id,description) VALUE "
        sql += '(\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\');'.format(int(row["id"]),
                                                                                                 int(row["window_id"]),
                                                                                                 str(int(row["type"])),
                                                                                                 row["name"],
                                                                                                 str(int(row["price"])),
                                                                                                 str(int(row["status"])),
                                                                                                 row["diningType"],
                                                                                                 row["unit"],
                                                                                                 str(row["image_id"])[:-4],
                                                                                                 row["description"])
        try:
            # 执行SQL语句
            # "id", "name", "address", "organization_id", "machine_id", "status"
            # print(int(row["status"]))
            # cursor.execute(sql, [row["id"], row["name"], row["address"], row["organization_id"], str(row["status"])])
            _cursor.execute(sql)
            # 提交事务
            _db.commit()
            print("成功插入窗口：" + sql)
        except Exception as e:
            print("异常！", str(e), "sql", sql)
            # 有异常，回滚事务
            _db.rollback()

    _cursor.close()
    _db.close()


if __name__ == '__main__':
    insert_window()

    """插入组织"""
    # 数据较少、手动添加

    """插入图片"""
    insert_image("./data/")
    print("》》》图片插入结束\n")

    """插入窗口"""
    insert_window()

    """插入saleUnit"""
    insert_saleUnit()
